﻿Imports System.Data.SqlClient
Imports System.Configuration

Public Class SQLHelper
    Public Event ErrorReport(ByVal message As String)
    Private _ConString As String
    Private _Gconnection As SqlConnection


    Public ReadOnly _reqConfigName As String = "MCSMTSConnect"

    Sub New()
        _ConString = ConfigurationManager.ConnectionStrings(_reqConfigName).ConnectionString()
        Try
            _Gconnection = New SqlConnection(_ConString)
        Catch ex As Exception
            RaiseEvent ErrorReport(ex.Message)
        End Try
    End Sub

    Sub New(ByVal ConnectionString As String)
        _ConString = ConnectionString
        Try
            _Gconnection = New SqlConnection(ConnectionString)
        Catch ex As Exception
            RaiseEvent ErrorReport(ex.Message)
        End Try
    End Sub

#Region "SQL Command"
    Public Function ExecutSQL(ByVal SQLCommand As String, Optional ByVal Parameter As List(Of SqlParameter) = Nothing) As Boolean
        Try
            If SQLCommand = "" Then
                RaiseEvent ErrorReport("SQL Is Null")
                Return False
                Exit Function
            End If
            _Gconnection.Open()
            Dim cmd As New SqlCommand(SQLCommand, _Gconnection)
            cmd.CommandType = CommandType.Text
            If Not Parameter Is Nothing Then
                cmd.Parameters.AddRange(Parameter.ToArray)
            End If
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            _Gconnection.Close()
            Return True
        Catch ex As Exception
            RaiseEvent ErrorReport(ex.Message)
            Return False
        Finally 
            _Gconnection.Close()
        End Try
    End Function
    Public Function GetDataRead(ByVal SQLCommand As String, Optional ByVal Parameter As List(Of SqlParameter) = Nothing) As SqlDataReader
        Try
            If SQLCommand = "" Then
                RaiseEvent ErrorReport("SQL Is Null")
                Return Nothing
                Exit Function
            End If
            _Gconnection.Open()
            Dim cmd As New SqlCommand(SQLCommand, _Gconnection)
            cmd.CommandType = CommandType.Text
            If Not Parameter Is Nothing Then
                cmd.Parameters.AddRange(Parameter.ToArray)
            End If
            Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Return dr
        Catch ex As Exception
            RaiseEvent ErrorReport(ex.Message)
            Return Nothing
        End Try
    End Function
    Public Function GetDataTable(ByVal SQLCommand As String, ByVal TableName As String, Optional ByVal Parameter As List(Of SqlParameter) = Nothing) As System.Data.DataTable
        Try
            Dim dt As New DataTable(TableName)
            Dim dtAdapter As SqlDataAdapter
            _Gconnection.Open()
            If SQLCommand = "" Then
                RaiseEvent ErrorReport("SQL Is Null")
                Return Nothing
                Exit Function
            End If
            dtAdapter = New SqlDataAdapter(SQLCommand, _Gconnection)
            dtAdapter.SelectCommand.CommandType = CommandType.Text
            If Not Parameter Is Nothing Then
                dtAdapter.SelectCommand.Parameters.AddRange(Parameter.ToArray)
            End If
            dt.Locale = System.Globalization.CultureInfo.CreateSpecificCulture("th-TH")
            dtAdapter.Fill(dt)
            dtAdapter.Dispose()
            _Gconnection.Close()
            Return dt
        Catch ex As Exception
            RaiseEvent ErrorReport(ex.Message)
            Return Nothing
        End Try
    End Function
#End Region

#Region "StoredProcedures"
    Public Function ExecutStoredProcedures(ByVal StoreName As String, Optional ByVal Parameter As List(Of SqlParameter) = Nothing) As Boolean
        Dim cmd As SqlCommand
        Try
            If StoreName = "" Then
                RaiseEvent ErrorReport("StoreName Is Null")
                Return False
                Exit Function
            End If
            _Gconnection.Open()
            cmd = New SqlCommand(StoreName, _Gconnection)
            cmd.CommandType = CommandType.StoredProcedure
            If Not Parameter Is Nothing Then
                cmd.Parameters.AddRange(Parameter.ToArray)
            End If
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            _Gconnection.Close()
            Return True
        Catch ex As Exception
            RaiseEvent ErrorReport(ex.Message)
            Return False
        Finally
            If Not cmd Is Nothing Then
                cmd.Dispose()
            End If
            _Gconnection.Close()
            _Gconnection.Close()
        End Try
    End Function
    Public Function GetStoredProcedures(ByVal StoreName As String, ByVal TableName As String, Optional ByVal Parameter As List(Of SqlParameter) = Nothing) As System.Data.DataTable
        Try
            Dim dt As New DataTable(TableName)
            Dim dtAdapter As SqlDataAdapter
            _Gconnection.Open()
            If StoreName = "" Then
                RaiseEvent ErrorReport("StoreName Is Null")
                Return Nothing
                Exit Function
            End If
            dtAdapter = New SqlDataAdapter(StoreName, _Gconnection)
            dtAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
            If Not Parameter Is Nothing Then
                dtAdapter.SelectCommand.Parameters.AddRange(Parameter.ToArray)
            End If
            'dt.Locale = System.Globalization.CultureInfo.CreateSpecificCulture("th-TH")
            dtAdapter.Fill(dt)
            dtAdapter.Dispose()
            _Gconnection.Close()
            Return dt
        Catch ex As Exception
            RaiseEvent ErrorReport(ex.Message)
            Return Nothing
        End Try
    End Function
#End Region

#Region "Transaction"
   
#End Region

End Class
